const mysql = require('mysql2/promise')

class DB {
  constructor() {
    this.config = {
      host: 'localhost',
      port: 3306,
      user: 'root',
      password: '123456',
      database: 'edu_system',
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0
    }
    
    this.pool = mysql.createPool(this.config)
    this.init()
  }

  async init() {
    await this.testConnection()
    await this.initializeDatabase()
  }

  async testConnection() {
    try {
      const conn = await this.pool.getConnection()
      console.log('✅ MySQL数据库连接成功')
      console.log('🛢️ 数据库配置:', {
        host: this.config.host,
        port: this.config.port,
        database: this.config.database
      })
      conn.release()
    } catch (err) {
      console.error('❌ MySQL数据库连接失败:', err.message)
      process.exit(1)
    }
  }

  async query(sql, params, connection) {
    if (connection) {
      const [rows] = await connection.execute(sql, params)
      return rows
    } else {
      const [rows] = await this.pool.execute(sql, params)
      return rows
    }
  }

  // 添加executeQuery作为query的别名，方便统一API
  async executeQuery(sql, params, connection) {
    return this.query(sql, params, connection);
  }

  // 执行不支持 prepared statement 的 SQL
  async executeRawQuery(sql) {
    const connection = await this.pool.getConnection()
    try {
      await connection.query(sql)
      console.log('成功执行 SQL 语句:', sql.substring(0, 50) + '...')
    } catch (error) {
      if (error.code === 'ER_ALREADY_EXISTS') {
        console.log('对象已存在，跳过创建')
      } else {
        throw error
      }
    } finally {
      connection.release()
    }
  }

  // 初始化数据库视图和定时任务
  async initializeDatabase() {
    try {
      console.log('正在初始化数据库视图和定时任务...')
      
      // 读取 SQL 文件内容
      const fs = require('fs')
      const path = require('path')
      const sqlContent = fs.readFileSync(
        path.join(__dirname, 'db', 'booking_status.sql'),
        'utf8'
      )

      // 预处理 SQL 语句，移除注释
      const cleanContent = sqlContent
        .split('\n')
        .filter(line => !line.trim().startsWith('--'))
        .join('\n')

      // 分割成独立的语句
      const viewStatement = cleanContent.match(/CREATE.*?VIEW.*?AS[\s\S]*?FROM.*?;/i)?.[0]
      const eventStatement = cleanContent.match(/CREATE.*?EVENT[\s\S]*?END;/i)?.[0]
      const procedureStatement = cleanContent.match(/CREATE.*?PROCEDURE[\s\S]*?END;/i)?.[0]

      // 执行视图创建
      if (viewStatement) {
        await this.executeRawQuery('DROP VIEW IF EXISTS v_bookings_status')
        await this.executeRawQuery(viewStatement)
      }

      // 启用事件调度器
      await this.executeRawQuery('SET GLOBAL event_scheduler = ON')

      // 执行事件创建
      if (eventStatement) {
        await this.executeRawQuery('DROP EVENT IF EXISTS update_booking_status')
        await this.executeRawQuery(eventStatement)
      }

      // 执行存储过程创建
      if (procedureStatement) {
        await this.executeRawQuery('DROP PROCEDURE IF EXISTS sp_update_booking_status')
        await this.executeRawQuery(procedureStatement)
      }

      console.log('数据库视图和定时任务初始化完成')
    } catch (error) {
      console.error('初始化数据库时出错:', error)
    }
  }

  // 添加事务支持
  async beginTransaction() {
    const connection = await this.pool.getConnection()
    await connection.beginTransaction()
    return connection
  }

  async commit(connection) {
    try {
      await connection.commit()
    } finally {
      connection.release()
    }
  }

  async rollback(connection) {
    try {
      await connection.rollback()
    } finally {
      connection.release()
    }
  }
}

module.exports = new DB()